﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace BuildForm
{
    public partial class Suppliers : Form
    {
        SqlConnection con;
        public Suppliers()
        {
            InitializeComponent();
            loadSupplier();
            btnDelete.Enabled = false;
            btnUpdate.Enabled = false;
            cbSearchBy.SelectedIndex = 0;
        }

        #region Function

        void loadSupplier()
        {

            gvSuppliers.Rows.Clear();

            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * FROM Production.Suppliers";
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    gvSuppliers.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(),
                        dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
                }
                dr.Close();
            }
            catch (Exception e)
            {

                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }

        }

        void searchSupplier()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                if (cbSearchBy.SelectedIndex == 0)
                {
                    cmd.CommandText = string.Format("SELECT * FROM Production.Suppliers WHERE companyname LIKE '%{0}%'", txtSearch.Text);
                }
                else
                {
                    if (cbSearchBy.SelectedIndex == 1)
                    {
                        cmd.CommandText = string.Format("SELECT * FROM Production.Suppliers WHERE contactname LIKE '%{0}%'", txtSearch.Text);
                    }

                }
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    gvSuppliers.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                        dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(),
                        dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }



        }

        void addSuppliers()
        {
            try
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_Suppliers_Insert";

                SqlParameter param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
                param.Value = txtCompanyName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@contacttitle", SqlDbType.NVarChar, 30);
                param.Value = txtContactTitle.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@contactname", SqlDbType.NVarChar, 30);
                param.Value = txtContactName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
                param.Value = txtAddress.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
                param.Value = txtCity.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
                param.Value = cbCountry.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
                param.Value = mtbPhone.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
                if (txtRegion.Text.Trim() == "")
                {
                    param.Value = DBNull.Value;
                }
                else param.Value = txtRegion.Text;

                cmd.Parameters.Add(param);

                param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
                if (mtbPostalCode.Text.Trim() == "")
                {
                    param.Value = DBNull.Value;
                }
                else param.Value = mtbPostalCode.Text;

                cmd.Parameters.Add(param);

                param = new SqlParameter("@fax", SqlDbType.NVarChar, 24);
                if (mtbFax.Text.Trim() == "")
                {
                    param.Value = DBNull.Value;
                }
                else param.Value = mtbFax.Text;

                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                gvSuppliers.Rows.Clear();
                loadSupplier();
                MessageBox.Show("Add Succeed!");


            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }

        }

        void deleteSuppliers()
        {
            try
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.sp_Suppliers_Delete";

                SqlParameter param = new SqlParameter("@supplierid", SqlDbType.Int);
                param.Value = txtSupplierId.Text;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                gvSuppliers.Rows.Clear();
                newSupplier();
                loadSupplier();
                MessageBox.Show("Delete Succeed!");


            }
            catch (Exception ex)
            {

                MessageBox.Show("Cannot delete");
            }
            finally
            {
                con.Close();
            }

        }

        void updateSuppliers()
        {
            try
            {
                txtSupplierId.Enabled = true;
                con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.sp_Suppliers_Update";

                SqlParameter param = new SqlParameter("@supplierid", SqlDbType.Int);
                param.Value = txtSupplierId.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
                param.Value = txtCompanyName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@contactname", SqlDbType.NVarChar, 30);
                param.Value = txtContactName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@contacttitle", SqlDbType.NVarChar, 30);
                param.Value = txtContactTitle.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
                param.Value = txtAddress.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
                param.Value = txtCity.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
                param.Value = txtRegion.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
                param.Value = mtbPostalCode.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
                param.Value = cbCountry.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
                param.Value = mtbPhone.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@fax", SqlDbType.NVarChar, 24);
                param.Value = mtbFax.Text;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                gvSuppliers.Rows.Clear();
                loadSupplier();
                errpro.Clear();

            }
            catch (Exception ex)
            {

                MessageBox.Show("Cannot update!");
            }
            finally
            {
                con.Close();
            }

        }

        void newSupplier()
        {
            try
            {
                errpro.Clear();
                txtSupplierId.Clear();
                txtCompanyName.Clear();
                txtContactName.Clear();
                txtContactTitle.Clear();
                txtAddress.Clear();
                txtCity.Clear();
                cbCountry.SelectedIndex = -1;
                txtRegion.Clear();
                mtbPostalCode.Clear();
                mtbPhone.Clear();
                mtbFax.Clear();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }


        }

        void selectRow()
        {
            try
            {
                if (gvSuppliers.SelectedRows.Count > 0)
                {
                    errpro.Clear();
                    DataGridViewRow r = gvSuppliers.SelectedRows[0];
                    txtSupplierId.Text = r.Cells["clSupplierId"].Value.ToString();
                    txtCompanyName.Text = r.Cells["clCompanyName"].Value.ToString();
                    txtContactName.Text = r.Cells["clContactName"].Value.ToString();
                    txtContactTitle.Text = r.Cells["clContactTitle"].Value.ToString();
                    txtAddress.Text = r.Cells["clAddress"].Value.ToString();
                    txtCity.Text = r.Cells["clCity"].Value.ToString();
                    txtRegion.Text = r.Cells["clRegion"].Value.ToString();
                    mtbPostalCode.Text = r.Cells["clPostalCode"].Value.ToString();
                    cbCountry.Text = r.Cells["clCountry"].Value.ToString();
                    mtbPhone.Text = r.Cells["clPhone"].Value.ToString();
                    mtbFax.Text = r.Cells["clFax"].Value.ToString();
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
        }

        bool validateAdd()
        {
            bool error = false;
            errpro.Clear();
            string CompanyName = txtCompanyName.Text;
            string ContactName = txtContactName.Text;
            string Address = txtAddress.Text;
            string ContactTitle = txtContactTitle.Text;
            string City = txtCity.Text;
            string Country = cbCountry.Text;
            string Phone = mtbPhone.Text;
            if (CompanyName.Length == 0)
            {
                errpro.SetError(txtCompanyName, "Empty Company Name");
                error = true;

            }


            //foreach (DataGridViewRow check in gvSuppliers.Rows)
            //{
            //    if (check.Cells["clCompanyName"].Value.ToString().Equals(CompanyName))
            //    {
            //        if (check.Cells["clAddress"].Value.ToString().Equals(Address)==false)
            //        epAddress1.SetError(txtAddress, "This is not address of "+CompanyName);
            //        error = true;
            //    }
            //    else epAddress1.Clear();
            //}


            if (ContactName.Length == 0)
            {
                errpro.SetError(txtContactName, "Empty Contact Name");
                error = true;

            }

            if (ContactTitle.Length == 0)
            {
                errpro.SetError(txtContactTitle, "Empty Contact Title");
                error = true;
            }


            if (Address.Length == 0)
            {
                errpro.SetError(txtAddress, "Empty Address");
                error = true;
            }

            if (City.Length == 0)
            {
                errpro.SetError(txtCity, "Empty City");
                error = true;
            }

            if (mtbPostalCode.Text.Length > 0)
            {
                if (mtbPostalCode.MaskFull == false)
                {
                    errpro.SetError(mtbPostalCode, "Invalid Postal code!");
                    error = true;
                }
            }


            if (mtbPhone.MaskCompleted == false)
            {
                errpro.SetError(mtbPhone, "Empty Phone");
                error = true;
            }
            if (mtbPhone.Text.Trim() == "(+  )    -")
            {
                errpro.SetError(mtbPhone, "Input Phone! (Ex: (111)111-1111)");
                error = true;
            }

            if (mtbFax.Text.Trim() != "(+  )    -")
            {
                if (mtbFax.MaskCompleted == false)
                {
                    errpro.SetError(mtbFax, "Please complete fill!");
                    error = true;
                }

            }


            if (cbCountry.SelectedIndex == -1)
            {
                errpro.SetError(cbCountry, "Empty Country");
                error = true;
            }





            return error;
        }

        #endregion

        #region Event
        private void btnReload_Click(object sender, EventArgs e)
        {
            loadSupplier();
            gvSuppliers.ClearSelection();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            errpro.Clear();
            if (validateAdd() == false)
            {
                DialogResult result = MessageBox.Show("Are you sure?", "Confirm?", MessageBoxButtons.YesNo);
                if (result == DialogResult.Yes)
                {
                    addSuppliers();
                    btnDelete.Enabled = true;
                    btnUpdate.Enabled = true;
                    btnAdd.Enabled = false;
                    
                }
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (validateAdd() == false)
            {
                DialogResult result = MessageBox.Show("Are you sure?", "Confirm?", MessageBoxButtons.YesNo);
                if (result == DialogResult.Yes)
                {
                    updateSuppliers();
                    MessageBox.Show("Update Succeed!");
                }


            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (gvSuppliers.SelectedRows.Count > 0)
            {

                DialogResult result = MessageBox.Show("Are you sure?", "Confirm?", MessageBoxButtons.YesNo);
                if (result == DialogResult.Yes)
                {

                    deleteSuppliers();
                }
            }

        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            btnAdd.Enabled = true;
            gvSuppliers.ClearSelection();
            newSupplier();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {

            gvSuppliers.Rows.Clear();
            searchSupplier();
        }

        private void gvSuppliers_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            btnAdd.Enabled = false;
            btnUpdate.Enabled = true;
            btnDelete.Enabled = true;
            selectRow();
        }
        #endregion





    }
}
